1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmStudentRecord1
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(AdmissionNo) as [Admission No],RTRIM(EnrollmentNo) as [Enrollment No], RTRIM(GRNo) as [GR No], RTRIM(UID) as [UID],Convert(DateTime,AdmissionDate,103) as [Admission Date], RTRIM(StudentName) as [Student Name], RTRIM(Gender) as [Gender],Convert(DateTime,DOB,103) as [DOB],RTRIM(Session) as Session,RTRIM(Caste) as [Category],RTRIM(Religion) as [Religion],RTRIM(FatherName) as [Father's Name], RTRIM(FatherCN) as [Father's CN], RTRIM(MotherName) as [Mother's Name],RTRIM(PermanentAddress) as [Permanent Address], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(Student.ContactNo) as [Contact No], RTRIM(EmailID) as [Email ID],RTRIM(SectionID) as [Section ID],RTRIM(ClassName) as [Class],RTRIM(SectionName) as Section,RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(LastSchoolAttended) as [Last School Attended],RTRIM(Result) as [Result],RTRIM(PassPerCentage) as [If Pass then %],RTRIM(Nationality) as [Nationality],RTRIM(Status) as [Status] from Student,Class,Section,SchoolInfo where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID order by StudentName", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "Student")
13 dgw.DataSource = ds.Tables("Student").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
21 Me.Close()
22 End Sub
23
24 Private Sub txtStudentName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStudentName.TextChanged
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 cmd = New SqlCommand("Select RTRIM(AdmissionNo) as [Admission No],RTRIM(EnrollmentNo) as [Enrollment No], RTRIM(GRNo) as [GR No], RTRIM(UID) as [UID],Convert(DateTime,AdmissionDate,103) as [Admission Date], RTRIM(StudentName) as [Student Name], RTRIM(Gender) as [Gender],Convert(DateTime,DOB,103) as [DOB],RTRIM(Session) as Session,RTRIM(Caste) as [Category],RTRIM(Religion) as [Religion],RTRIM(FatherName) as [Father's Name], RTRIM(FatherCN) as [Father's CN], RTRIM(MotherName) as [Mother's Name],RTRIM(PermanentAddress) as [Permanent Address], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(Student.ContactNo) as [Contact No], RTRIM(EmailID) as [Email ID],RTRIM(SectionID) as [Section ID],RTRIM(ClassName) as [Class],RTRIM(SectionName) as Section,RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(LastSchoolAttended) as [Last School Attended],RTRIM(Result) as [Result],RTRIM(PassPerCentage) as [If Pass then %],RTRIM(Nationality) as [Nationality],RTRIM(Status) as [Status] from Student,Class,Section,SchoolInfo where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and StudentName like '" & txtStudentName.Text & "%' order by StudentName", con)
29 adp = New SqlDataAdapter(cmd)
30 ds = New DataSet()
31 adp.Fill(ds, "Student")
32 dgw.DataSource = ds.Tables("Student").DefaultView
33 con.Close()
34 Catch ex As Exception
35 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
36 End Try
37 End Sub
38
39 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
40 Try
41 con = New SqlConnection(cs)
42 con.Open()
43 cmd = New SqlCommand("Select RTRIM(AdmissionNo) as [Admission No],RTRIM(EnrollmentNo) as [Enrollment No], RTRIM(GRNo) as [GR No], RTRIM(UID) as [UID],Convert(DateTime,AdmissionDate,103) as [Admission Date], RTRIM(StudentName) as [Student Name], RTRIM(Gender) as [Gender],Convert(DateTime,DOB,103) as [DOB],RTRIM(Session) as Session,RTRIM(Caste) as [Category],RTRIM(Religion) as [Religion],RTRIM(FatherName) as [Father's Name], RTRIM(FatherCN) as [Father's CN], RTRIM(MotherName) as [Mother's Name],RTRIM(PermanentAddress) as [Permanent Address], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(Student.ContactNo) as [Contact No], RTRIM(EmailID) as [Email ID],RTRIM(SectionID) as [Section ID],RTRIM(ClassName) as [Class],RTRIM(SectionName) as Section,RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(LastSchoolAttended) as [Last School Attended],RTRIM(Result) as [Result],RTRIM(PassPerCentage) as [If Pass then %],RTRIM(Nationality) as [Nationality],RTRIM(Status) as [Status] from Student,Class,Section,SchoolInfo where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Session=@d1 and ClassName=@d2 and SectionName=@d3 order by StudentName", con)
44 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
45 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
46 cmd.Parameters.AddWithValue("@d3", cmbSection.Text)
47 adp = New SqlDataAdapter(cmd)
48 ds = New DataSet()
49 adp.Fill(ds, "Student")
50 dgw.DataSource = ds.Tables("Student").DefaultView
51 con.Close()
52 Catch ex As Exception
53 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
54 End Try
55 End Sub
56
57 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
58 Try
59 con = New SqlConnection(cs)
60 con.Open()
61 cmd = New SqlCommand("Select RTRIM(AdmissionNo) as [Admission No],RTRIM(EnrollmentNo) as [Enrollment No], RTRIM(GRNo) as [GR No], RTRIM(UID) as [UID],Convert(DateTime,AdmissionDate,103) as [Admission Date], RTRIM(StudentName) as [Student Name], RTRIM(Gender) as [Gender],Convert(DateTime,DOB,103) as [DOB],RTRIM(Session) as Session,RTRIM(Caste) as [Category],RTRIM(Religion) as [Religion],RTRIM(FatherName) as [Father's Name], RTRIM(FatherCN) as [Father's CN], RTRIM(MotherName) as [Mother's Name],RTRIM(PermanentAddress) as [Permanent Address], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(Student.ContactNo) as [Contact No], RTRIM(EmailID) as [Email ID],RTRIM(SectionID) as [Section ID],RTRIM(ClassName) as [Class],RTRIM(SectionName) as Section,RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(LastSchoolAttended) as [Last School Attended],RTRIM(Result) as [Result],RTRIM(PassPerCentage) as [If Pass then %],RTRIM(Nationality) as [Nationality],RTRIM(Status) as [Status] from Student,Class,Section,SchoolInfo where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and AdmissionDate between @d1 and @d2 order by StudentName", con)
62 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
63 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
64 adp = New SqlDataAdapter(cmd)
65 ds = New DataSet()
66 adp.Fill(ds, "Student")
67 dgw.DataSource = ds.Tables("Student").DefaultView
68 con.Close()
69 Catch ex As Exception
70 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
71 End Try
72 End Sub
73
74 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
75 Try
76 con = New SqlConnection(cs)
77 con.Open()
78 cmd = New SqlCommand("Select RTRIM(AdmissionNo) as [Admission No],RTRIM(EnrollmentNo) as [Enrollment No], RTRIM(GRNo) as [GR No], RTRIM(UID) as [UID],Convert(DateTime,AdmissionDate,103) as [Admission Date], RTRIM(StudentName) as [Student Name], RTRIM(Gender) as [Gender],Convert(DateTime,DOB,103) as [DOB],RTRIM(Session) as Session,RTRIM(Caste) as [Category],RTRIM(Religion) as [Religion],RTRIM(FatherName) as [Father's Name], RTRIM(FatherCN) as [Father's CN], RTRIM(MotherName) as [Mother's Name],RTRIM(PermanentAddress) as [Permanent Address], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(Student.ContactNo) as [Contact No], RTRIM(EmailID) as [Email ID],RTRIM(SectionID) as [Section ID],RTRIM(ClassName) as [Class],RTRIM(SectionName) as Section,RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(LastSchoolAttended) as [Last School Attended],RTRIM(Result) as [Result],RTRIM(PassPerCentage) as [If Pass then %],RTRIM(Nationality) as [Nationality],RTRIM(Status) as [Status] from Student,Class,Section,SchoolInfo where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Classname=@d1 and Caste=@d2 order by StudentName", con)
79 cmd.Parameters.AddWithValue("@d1", cmbClass1.Text)
80 cmd.Parameters.AddWithValue("@d2", cmbCategory.Text)
81 adp = New SqlDataAdapter(cmd)
82 ds = New DataSet()
83 adp.Fill(ds, "Student")
84 dgw.DataSource = ds.Tables("Student").DefaultView
85 con.Close()
86 Catch ex As Exception
87 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
88 End Try
89 End Sub
90 Sub fillSession()
91 Try
92 con = New SqlConnection(cs)
93 con.Open()
94 adp = New SqlDataAdapter()
95 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM Student", con)
96 ds = New DataSet("ds")
97 adp.Fill(ds)
98 dtable = ds.Tables(0)
99 cmbSession.Items.Clear()
100 For Each drow As DataRow In dtable.Rows
101 cmbSession.Items.Add(drow(0).ToString())
102 Next
103 Catch ex As Exception
104 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
105 End Try
106 End Sub
107 Sub fillClass()
108 Try
109 con = New SqlConnection(cs)
110 con.Open()
111 adp = New SqlDataAdapter()
112 adp.SelectCommand = New SqlCommand("SELECT distinct (ClassName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.Classname", con)
113 ds = New DataSet("ds")
114 adp.Fill(ds)
115 dtable = ds.Tables(0)
116 cmbClass1.Items.Clear()
117 For Each drow As DataRow In dtable.Rows
118 cmbClass1.Items.Add(drow(0).ToString())
119 Next
120 Catch ex As Exception
121 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
122 End Try
123 End Sub
124
125 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
126 Try
127 cmbClass.Enabled = True
128 con = New SqlConnection(cs)
129 con.Open()
130 Dim ct As String = "SELECT distinct RTRIM(ClassName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and Session=@d1"
131 cmd = New SqlCommand(ct)
132 cmd.Connection = con
133 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
134 rdr = cmd.ExecuteReader()
135 cmbClass.Items.Clear()
136 While rdr.Read
137 cmbClass.Items.Add(rdr(0))
138 End While
139 con.Close()
140 Catch ex As Exception
141 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
142 End Try
143
144 End Sub
145
146 Private Sub cmbClass_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
147 Try
148 cmbSection.Enabled = True
149 con = New SqlConnection(cs)
150 con.Open()
151 Dim ct As String = "SELECT distinct RTRIM(SectionName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and Session=@d1 and ClassName=@d2"
152 cmd = New SqlCommand(ct)
153 cmd.Connection = con
154 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
155 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
156 rdr = cmd.ExecuteReader()
157 cmbSection.Items.Clear()
158 While rdr.Read
159 cmbSection.Items.Add(rdr(0))
160 End While
161 con.Close()
162 Catch ex As Exception
163 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
164 End Try
165 End Sub
166
167 Private Sub txtAdmissionNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtAdmissionNo.TextChanged
168 Try
169 con = New SqlConnection(cs)
170 con.Open()
171 cmd = New SqlCommand("Select RTRIM(AdmissionNo) as [Admission No],RTRIM(EnrollmentNo) as [Enrollment No], RTRIM(GRNo) as [GR No], RTRIM(UID) as [UID],Convert(DateTime,AdmissionDate,103) as [Admission Date], RTRIM(StudentName) as [Student Name], RTRIM(Gender) as [Gender],Convert(DateTime,DOB,103) as [DOB],RTRIM(Session) as Session,RTRIM(Caste) as [Category],RTRIM(Religion) as [Religion],RTRIM(FatherName) as [Father's Name], RTRIM(FatherCN) as [Father's CN], RTRIM(MotherName) as [Mother's Name],RTRIM(PermanentAddress) as [Permanent Address], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(Student.ContactNo) as [Contact No], RTRIM(EmailID) as [Email ID],RTRIM(SectionID) as [Section ID],RTRIM(ClassName) as [Class],RTRIM(SectionName) as Section,RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(LastSchoolAttended) as [Last School Attended],RTRIM(Result) as [Result],RTRIM(PassPerCentage) as [If Pass then %],RTRIM(Nationality) as [Nationality],RTRIM(Status) as [Status] from Student,Class,Section,SchoolInfo where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and AdmissionNo like '" & txtAdmissionNo.Text & "%' order by StudentName", con)
172 adp = New SqlDataAdapter(cmd)
173 ds = New DataSet()
174 adp.Fill(ds, "Student")
175 dgw.DataSource = ds.Tables("Student").DefaultView
176 con.Close()
177 Catch ex As Exception
178 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
179 End Try
180 End Sub
181 Sub Reset()
182 txtAdmissionNo.Text = ""
183 txtStudentName.Text = ""
184 cmbCategory.SelectedIndex = -1
185 cmbClass.SelectedIndex = -1
186 cmbClass1.SelectedIndex = -1
187 cmbSection.SelectedIndex = -1
188 cmbSession.SelectedIndex = -1
189 txtGRNo.Text = ""
190 cmbClass.Enabled = False
191 cmbSection.Enabled = False
192 dtpDateFrom.Text = Today
193 dtpDateTo.Text = Today
194 GetData()
195 End Sub
196 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
197 Reset()
198 End Sub
199
200 Private Sub frmStudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
201 fillClass()
202 fillSession()
203 GetData()
204 End Sub
205
206 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
207 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
208 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
209 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
210 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
211 End If
212 Dim b As Brush = SystemBrushes.ControlText
213 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
214
215 End Sub
216
217 Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
218 Dim rowsTotal, colsTotal As Short
219 Dim I, j, iC As Short
220 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
221 Dim xlApp As New Excel.Application
222 Try
223 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
224 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
225 xlApp.Visible = True
226
227 rowsTotal = dgw.RowCount
228 colsTotal = dgw.Columns.Count - 1
229 With excelWorksheet
230 .Cells.Select()
231 .Cells.Delete()
232 For iC = 0 To colsTotal
233 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
234 Next
235 For I = 0 To rowsTotal - 1
236 For j = 0 To colsTotal
237 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
238 Next j
239 Next I
240 .Rows("1:1").Font.FontStyle = "Bold"
241 .Rows("1:1").Font.Size = 12
242
243 .Cells.Columns.AutoFit()
244 .Cells.Select()
245 .Cells.EntireColumn.AutoFit()
246 .Cells(1, 1).Select()
247 End With
248 Catch ex As Exception
249 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
250 Finally
251 'RELEASE ALLOACTED RESOURCES
252 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
253 xlApp = Nothing
254 End Try
255 End Sub
256
257 Private Sub txtGRNo_TextChanged(sender As Object, e As EventArgs) Handles txtGRNo.TextChanged
258 Try
259 con = New SqlConnection(cs)
260 con.Open()
261 cmd = New SqlCommand("Select RTRIM(AdmissionNo) as [Admission No],RTRIM(EnrollmentNo) as [Enrollment No], RTRIM(GRNo) as [GR No], RTRIM(UID) as [UID],Convert(DateTime,AdmissionDate,103) as [Admission Date], RTRIM(StudentName) as [Student Name], RTRIM(Gender) as [Gender],Convert(DateTime,DOB,103) as [DOB],RTRIM(Session) as Session,RTRIM(Caste) as [Category],RTRIM(Religion) as [Religion],RTRIM(FatherName) as [Father's Name], RTRIM(FatherCN) as [Father's CN], RTRIM(MotherName) as [Mother's Name],RTRIM(PermanentAddress) as [Permanent Address], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(Student.ContactNo) as [Contact No], RTRIM(EmailID) as [Email ID],RTRIM(SectionID) as [Section ID],RTRIM(ClassName) as [Class],RTRIM(SectionName) as Section,RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(LastSchoolAttended) as [Last School Attended],RTRIM(Result) as [Result],RTRIM(PassPerCentage) as [If Pass then %],RTRIM(Nationality) as [Nationality],RTRIM(Status) as [Status],Photo from Student,Class,Section,SchoolInfo where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and GRNo like '" & txtGRNo.Text & "%' order by StudentName", con)
262 adp = New SqlDataAdapter(cmd)
263 ds = New DataSet()
264 adp.Fill(ds, "Student")
265 dgw.DataSource = ds.Tables("Student").DefaultView
266 con.Close()
267 Catch ex As Exception
268 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
269 End Try
270 End Sub
271 End Class
